# ahp_engine.py
import os
from dotenv import load_dotenv
import psycopg2
import psycopg2.extras
from psycopg2 import pool
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import os
from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, Image as RLImage, Table, TableStyle, PageBreak
from reportlab.platypus import BaseDocTemplate, Frame, PageTemplate
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib import colors
from reportlab.pdfgen import canvas
from datetime import datetime
# pdf生成中文显示问题
from reportlab.pdfbase.ttfonts import TTFont
from reportlab.pdfbase import pdfmetrics
# 注册中文字体
pdfmetrics.registerFont(TTFont('simhei', 'simhei.ttf'))

# 绘图中文乱码的问题
import matplotlib
matplotlib.use('Agg')
matplotlib.rc("font",family="SimHei")
# 加载.env文件中的环境变量
load_dotenv();

# 随机一致性指标表
RI_VALUES = {
    1: 0.00,
    2: 0.00,
    3: 0.58,
    4: 0.90,
    5: 1.12,
    6: 1.24,
    7: 1.32,
    8: 1.41,
    9: 1.45,
    10: 1.49
}

# 预设判断矩阵
PRESET_JUDGMENT_MATRICES = {
    "standard": [
        [1,   3,   5,   4,   2,   6],
        [1/3, 1,   3,   2, 1/2, 4],
        [1/5, 1/3, 1, 1/2, 1/4, 2],
        [1/4, 1/2, 2,   1, 1/3, 3],
        [1/2, 2,   4,   3,   1, 5],
        [1/6, 1/4, 1/2, 1/3, 1/5, 1]
    ],
    "tourism_heavy": [
        [1,   5,   7,   6,   3,   8],
        [1/5, 1,   3,   2, 1/2, 5],
        [1/7, 1/3, 1, 1/2, 1/4, 3],
        [1/6, 1/2, 2,   1, 1/3, 4],
        [1/3, 2,   4,   3,   1, 6],
        [1/8, 1/5, 1/3, 1/4, 1/6, 1]
    ],
    "hotel_heavy": [
        [1,   1/3, 1/2, 1/2, 1/4, 1/5],
        [3,   1,   2,   1,   1/2, 1/3],
        [2, 1/2,   1, 1/2, 1/3, 1/4],
        [2,   1,   2,   1,   1/2, 1/3],
        [4,   2,   3,   2,   1,   1/2],
        [5,   3,   4,   3,   2,     1]
    ],
    "traffic_priority": [
        [1,   1/2, 1/3,   1,   1/4, 1/3],
        [2,   1,   1/2,   2,   1/3, 1/2],
        [3,   2,   1,     3,   1/2, 1],
        [1,   1/2, 1/3,   1,   1/4, 1/3],
        [4,   3,   2,     4,   1,   2],
        [3,   2,   1,     3,   1/2, 1]
    ]
}

# 初始化连接池
class DatabaseConnectionPool:
    __pool = None

    @staticmethod
    def initialize():
        # 获取数据库配置
        db_config = {
            "host": os.getenv("DB_HOST"),
            "port": int(os.getenv("DB_PORT")),
            "database": os.getenv("DB_NAME"),
            "user": os.getenv("DB_USER"),
            "password": os.getenv("DB_PASSWORD"),
            "minconn": 1,  # 最少连接数
            "maxconn": 5   # 最大连接数（根据你的负载调整）
        }
        DatabaseConnectionPool.__pool = psycopg2.pool.ThreadedConnectionPool(**db_config)
        print("[INFO] 数据库连接池已初始化")

    @staticmethod
    def get_connection():
        if DatabaseConnectionPool.__pool is None:
            raise Exception("连接池未初始化！请先调用 DatabaseConnectionPool.initialize()")
        return DatabaseConnectionPool.__pool.getconn()

    @staticmethod
    def put_connection(conn):
        if DatabaseConnectionPool.__pool:
            DatabaseConnectionPool.__pool.putconn(conn)


def fetch_poi_data():
    """从数据库中获取POI统计数据"""
    conn = None
    try:
        conn = DatabaseConnectionPool.get_connection()
        query = """
            SELECT r.name, ap.primary_class, COUNT(ap.id) AS total
            FROM public.tb_region r
            LEFT JOIN public.vw_all_ahp_poi ap ON public.ST_Contains(r.geom, ap.geom)
            GROUP BY r.name, ap.primary_class;
            """
        df = pd.read_sql(query, conn)
        return df
    except Exception as e:
        print(f"[ERROR] 获取 POI 数据失败: {str(e)}")
        return pd.DataFrame()
    finally:
        # 将连接放回连接池
        if conn:
            DatabaseConnectionPool.put_connection(conn)

def preprocess_data(df):
    df.columns = ['district', 'poi_type', 'count']
    df_wide = df.pivot(index='district', columns='poi_type', values='count').fillna(0)
    df_normalized = df_wide.div(df_wide.max(), axis=1)
    return df_wide, df_normalized

def is_valid_judgment_matrix(matrix):
    """验证是否是一个有效的判断矩阵（方阵、正数、互反）"""
    matrix = np.array(matrix)
    if len(matrix.shape) != 2 or matrix.shape[0] != matrix.shape[1]:
        return False
    for i in range(len(matrix)):
        for j in range(len(matrix)):
            if not np.isclose(matrix[i][j], 1 / matrix[j][i]) and i != j:
                return False
    return True

def calculate_ahp_weights(judgment_matrix=None,preset=None):
    """
    构建判断矩阵并计算各指标权重及一致性比率 CR

    参数:
    - judgment_matrix (list of lists): 可选，用户提供的判断矩阵

    返回:
    - weights_dict (dict): 权重字典
    - cr (float): 一致性比率（用于判断合理性）
    """
    # if judgment_matrix is None:
    #     judgment_matrix = [
    #         [1,   3,   5,   4,   2,   6],
    #         [1/3, 1,   3,   2, 1/2, 4],
    #         [1/5, 1/3, 1, 1/2, 1/4, 2],
    #         [1/4, 1/2, 2,   1, 1/3, 3],
    #         [1/2, 2,   4,   3,   1, 5],
    #         [1/6, 1/4, 1/2, 1/3, 1/5, 1]
    #     ]
    if judgment_matrix is not None:
        # 如果用户提供了判断矩阵,则使用它
        pass
    elif preset and preset in PRESET_JUDGMENT_MATRICES:
        #  如果用户提供了预设的判断矩阵名称,则使用它
        judgment_matrix = PRESET_JUDGMENT_MATRICES[preset]
    else:
        # 默认矩阵
        judgment_matrix = PRESET_JUDGMENT_MATRICES['standard']

    # print("preset::",preset)
    # print("judgment_matrix::",judgment_matrix)

    # judgment_matrix = np.array(judgment_matrix, dtype=np.float64)
    judgment_matrix = np.array(judgment_matrix, dtype=float)
    n = judgment_matrix.shape[0]

    # 计算最大特征根和特征向量
    eigenvalues, eigenvectors = np.linalg.eig(judgment_matrix)
    max_eigenvalue = eigenvalues[np.argmax(eigenvalues)].real
    weights = eigenvectors[:, np.argmax(eigenvalues)].real
    weights /= weights.sum()

    # 将numpy array转换为list
    weights_list = weights.tolist()

    # print('计算AHP权重和一致性比率::',weights_list)

    # 计算 CI 和 CR
    ci = (max_eigenvalue - n) / (n - 1)
    ri = RI_VALUES.get(n, 0.0)
    cr = ci / ri if ri != 0 else 0.0

    return dict(zip(['旅游景点', '酒店住宿', '餐饮美食', '交通设施', '商场超市', '医院药店'], weights_list)), cr

def calculate_scores(df_normalized, weights):
    scores = {}
    for district, row in df_normalized.iterrows():
        score = sum(row[ptype] * weights[ptype] for ptype in weights if ptype in row.index)
        scores[district] = score
    return pd.DataFrame.from_dict(scores, orient='index', columns=['Score']).sort_values(by='Score', ascending=False)


def plot_scores(score_df, output_path):
    """
    绘制城市得分排名柱状图，并保存为图片文件
    
    参数:
    - score_df: 包含城市得分的 DataFrame
    - output_path: 图片输出路径（不带扩展名）
    """
    plt.figure(figsize=(10, 6))
    score_df.plot(kind='barh', color='skyblue')
    plt.title('临沂市下属区划旅游吸引力综合评分')
    plt.xlabel('得分')
    plt.ylabel('区划名称')
    plt.gca().invert_yaxis()  # 让最高分在最上面
    plt.tight_layout()

    # 确保目标路径存在
    os.makedirs(os.path.dirname(output_path), exist_ok=True)
    
    # 保存图片
    plt.savefig(output_path, dpi=300, bbox_inches='tight')
    plt.close()

def get_table_style():
    return TableStyle([
        ('FONTNAME', (0, 0), (-1, -1), 'simhei'),   # 设置全局字体为 simhei
        ('FONTSIZE', (0, 0), (-1, 0), 12),
        ('BACKGROUND', (0, 0), (-1, 0), colors.lightblue),
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
        ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
        ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
        ('BACKGROUND', (0, 1), (-1, -1), colors.beige),
        ('GRID', (0, 0), (-1, -1), 1, colors.black)
    ])


def generate_pdf_report(score_df, weights, image_path, original_matrix, normalized_matrix, output_path):
    """
    生成 AHP 分析的 PDF 报告，并包含原始矩阵和归一化矩阵
    
    参数:
    - score_df: 区域得分 DataFrame
    - weights: 权重字典
    - image_path: 图像路径（柱状图）
    - original_matrix: 原始 POI 矩阵（DataFrame）
    - normalized_matrix: 归一化后的矩阵（DataFrame）
    - output_path: 输出 PDF 路径
    """
    # 格式化矩阵数值，保留最多6位小数
    original_matrix = original_matrix.round(6)
    normalized_matrix = normalized_matrix.round(6)

    # 可选：将 float 类型转为 str 并去掉尾部 .0（更美观）
    original_matrix = original_matrix.astype(float).round(6).astype(str).replace(r'\.0+$', '', regex=True)
    normalized_matrix = normalized_matrix.astype(float).round(6).astype(str).replace(r'\.0+$', '', regex=True)

    # 初始化文档
    # doc = SimpleDocTemplate(output_path)
    doc = BaseDocTemplate(output_path)

    # 定义页面框架
    header_height = 30  # 页眉高度，单位像素
    frame = Frame(doc.leftMargin, doc.bottomMargin, doc.width, doc.height - header_height, id='normal')

    # 定义页眉和页脚函数
    def add_page_number(canvas, doc):
        page_num = canvas.getPageNumber()
        text = f"第 {page_num} 页"
        canvas.setFont('simhei', 10)
        canvas.drawCentredString(doc.width / 2 + doc.leftMargin, 15, text)

    def add_header(canvas, doc):
        header_text = "临沂市下辖城市旅游吸引力 AHP 层次分析法报告"
        canvas.setFont('simhei', 12)
        canvas.drawString(doc.leftMargin, doc.height + doc.topMargin - 20, header_text)
        canvas.line(doc.leftMargin, doc.height + doc.topMargin - 25, doc.width + doc.rightMargin, doc.height + doc.topMargin - 25)

    # 定义页面模板
    doc.addPageTemplates([
        PageTemplate(id='AllPages', frames=frame,
                     onPage=add_page_number,
                     onPageEnd=add_header)
    ])


    # 自定义样式表
    styles = getSampleStyleSheet()
    
    # 添加自定义中文字体样式
    styles.add(ParagraphStyle(name='NormalCN', fontName='simhei', fontSize=12, leading=14))
    styles.add(ParagraphStyle(name='TitleCN', fontName='simhei', fontSize=18, leading=22, alignment=1))  # 居中标题
    styles.add(ParagraphStyle(name='Heading2CN', fontName='simhei', fontSize=14, leading=16))
    styles.add(ParagraphStyle(name='NoteCN', fontName='simhei', fontSize=10, leading=12, textColor=colors.darkslategray))
    
    elements = []

    # 👇 新增一个 Spacer，为页眉腾出空间
    elements.append(Spacer(1, header_height * 2))  # 腾出两倍页眉高度的空间

    # 标题
    title = Paragraph("AHP 层次分析法报告", styles['TitleCN'])
    elements.append(title)
    elements.append(Spacer(1, 12))

    # 时间戳
    timestamp = datetime.now().strftime("%Y-%m-%d %H:%M")
    elements.append(Paragraph(f"生成时间：{timestamp}", styles['NormalCN']))
    elements.append(Spacer(1, 24))

    # —————————— 新增内容开始 ——————————
    elements.append(Paragraph("单位说明", styles['Heading2CN']))
    elements.append(Paragraph("🔹 POI 数量单位为‘个’", styles['NoteCN']))
    elements.append(Paragraph("🔹 权重值为归一化后指标权重", styles['NoteCN']))
    elements.append(Paragraph("🔹 得分值范围为 [0, 1]，保留至第4位小数", styles['NoteCN']))
    elements.append(Paragraph("🔹 所有矩阵数值已保留至第6位小数", styles['NoteCN']))
    elements.append(Spacer(1, 24))


    # 获取最大列数用于计算列宽
    max_col_count = max(len(original_matrix.columns) + 1, 6)  # +1 是因为加上了区域名列
    col_width = (doc.pagesize[0] - 100) / max_col_count  # 保留边距后平均分配列宽

    # 原始 POI 矩阵
    elements.append(Paragraph("原始 POI 统计矩阵", styles['Heading2CN']))
    poi_data = [list(original_matrix.columns)]  # 表头
    for index, row in original_matrix.iterrows():
        poi_data.append([index] + list(row.values))
    poi_table = Table(poi_data, colWidths=[col_width] * len(poi_data[0]))
    poi_table.setStyle(get_table_style())
    elements.append(poi_table)
    # 添加数据精度说明
    elements.append(Paragraph("（说明：数值已保留至第6位小数）", styles['NoteCN']))
    elements.append(Spacer(1, 24))

    # 归一化矩阵
    elements.append(Paragraph("归一化矩阵", styles['Heading2CN']))
    norm_data = [list(normalized_matrix.columns)]  # 表头
    for index, row in normalized_matrix.iterrows():
        norm_data.append([index] + list(row.values))
    norm_table = Table(norm_data, colWidths=[col_width] * len(norm_data[0]))
    norm_table.setStyle(get_table_style())
    elements.append(norm_table)
    # 添加数据精度说明
    elements.append(Paragraph("（说明：数值已保留至第6位小数）", styles['NoteCN']))
    elements.append(Spacer(1, 24))

    # 权重表
    elements.append(Paragraph("指标权重分布", styles['Heading2CN']))
    weight_data = [["POI 类型", "权重"]]
    for key, value in weights.items():
        weight_data.append([key, f"{value:.4f}"])
    weight_table = Table(weight_data)
    weight_table.setStyle(get_table_style())
    elements.append(weight_table)
    elements.append(Spacer(1, 24))

    # 得分表
    elements.append(Paragraph("区域综合得分", styles['Heading2CN']))
    score_data = [["区域", "得分"]]
    for index, row in score_df.iterrows():
        score_data.append([index, f"{row['Score']:.4f}"])
    score_table = Table(score_data)
    score_table.setStyle(get_table_style())
    elements.append(score_table)
    elements.append(Spacer(1, 24))

    elements.append(Paragraph("图例说明", styles['Heading2CN']))
    elements.append(Paragraph("🔹 柱状图颜色为 SkyBlue，表示区域旅游吸引力综合得分", styles['NoteCN']))
    elements.append(Paragraph("🔹 色块背景色含义：浅蓝为表头行，米黄为数据行", styles['NoteCN']))
    elements.append(Paragraph("🔹 表格字体为 SimHei（黑体），确保中文显示正常", styles['NoteCN']))
    elements.append(Spacer(1, 24))

    # 图像
    elements.append(Paragraph("得分排名可视化", styles['Heading2CN']))
    elements.append(RLImage(image_path, width=400, height=250))
    elements.append(Paragraph("🔹 柱状图横轴为得分，纵轴为区划名称", styles['NoteCN']))
    elements.append(Spacer(1, 24))

    # 结论
    elements.append(Paragraph("结论与建议", styles['Heading2CN']))
    conclusion_text = (
        "本报告基于 AHP 层次分析法对临沂市各区域旅游吸引力进行评估。"
        "最终得分表明各区域的相对优势。建议根据实际情况调整判断矩阵以提高一致性比率（CR）。"
    )
    elements.append(Paragraph(conclusion_text, styles['NormalCN']))

    # 构建 PDF
    doc.build(elements)

def save_analysis_record(conn, data):
    """
    将一次 AHP 分析结果保存到 PostgreSQL 数据库中

    参数:
    - conn: 数据库连接对象
    - data: dict，包含所有字段数据
    """
    cursor = conn.cursor()
    insert_sql = """
    INSERT INTO public.ahp_analysis_records (
        request_id, user_ip, is_custom_matrix, custom_matrix,
        weights, cr, scores, report_dir, report_zip_url,
        report_pdf_url, ranking_image_url, warning
    ) VALUES (
        %(request_id)s, %(user_ip)s, %(is_custom_matrix)s, %(custom_matrix)s,
        %(weights)s, %(cr)s, %(scores)s, %(report_dir)s, %(report_zip_url)s,
        %(report_pdf_url)s, %(ranking_image_url)s, %(warning)s
    )
    """
    psycopg2.extras.execute_batch(cursor, insert_sql, [data])
    conn.commit()